package com.innazis.jfrogsurvey.DAL;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.innazis.jfrogsurvey.config.Constants;

/**
 * Created by Inna on 03.06.14.
 */
public class DBHelper extends SQLiteOpenHelper {

    // table surveys
    static String SQL_CREATE_TABLE_SURVEYS = "CREATE TABLE "
            + Constants.TBL_SURVEYS + "( "
            + Constants.survey_id_p + " INTEGER PRIMARY KEY, "
            + Constants.survey_name_p + " STRING, "
            + Constants.survey_title_p + " TEXT,"
            + Constants.questions_num_p + " INTEGER,"
            + Constants.is_sent_p + " INTEGER,"
            + Constants.submitted_datetime_p + " TEXT,"
            + Constants.user_data_p + " TEXT );";

    // table questions for surveys
    static String SQL_CREATE_TABLE_QUESTIONS = "CREATE TABLE "
            + Constants.TBL_QUESTIONS + "( "
            + Constants.question_id_p + " INTEGER NOT NULL, "
            + Constants.survey_id_p + " INTEGER NOT NULL, "
            + Constants.question_title_p + " STRING, "
            + Constants.question_order_p + " INTEGER, "
            + Constants.answers_num_p + " INTEGER );";

    // table answers for questions
    static String SQL_CREATE_TABLE_ANSWERS = "CREATE TABLE "
            + Constants.TBL_ANSWERS + " ( "
            + Constants.answer_id_p + " INTEGER NOT NULL, "
            + Constants.question_id_p + " INTEGER NOT NULL, "
            + Constants.survey_id_p + " INTEGER NOT NULL, "
            + Constants.answer_order_p + " INTEGER, "
            + Constants.answer_text_p + " STRING,"
            + Constants.object_name_p + " NUMERIC,"
            + Constants.is_checked_p + " INTEGER)";

    // Constructor, defines the DB name & version
    public DBHelper(Context context) {
        super(context, Constants.DATABASE_NAME, null,
                Constants.DATABASE_VERSION);
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE_TABLE_SURVEYS);
        db.execSQL(SQL_CREATE_TABLE_QUESTIONS);
        db.execSQL(SQL_CREATE_TABLE_ANSWERS);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + Constants.TBL_SURVEYS);
        db.execSQL("DROP TABLE IF EXISTS " + Constants.TBL_QUESTIONS);
        db.execSQL("DROP TABLE IF EXISTS " + Constants.TBL_ANSWERS);
        onCreate(db);
    }
}
